package cn.edu.sanxiau.www.dao.impl;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import cn.edu.sanxiau.www.dao.ExamDao;
import cn.edu.sanxiau.www.model.Exam;
import cn.edu.sanxiau.www.model.PaiRoom;
import frame.utils.dbutil.JdbcUtils;

public class ExamDaoImpl implements ExamDao {

	@Override
	public List<Exam> queryAllExam() {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		// 查询数据库
		try {
			conn = JdbcUtils.getConnection();
			String sql = "SELECT * FROM exam";
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();

			List<Exam> exam_db_list = new ArrayList<Exam>();

			while (rs.next()) {
				Exam exam_db = new Exam();
				exam_db.setExamId(rs.getInt("ExamId"));
				exam_db.setExamName(rs.getString("examName"));
				exam_db.setExamType(rs.getString("examType")); 
				exam_db.setExamState(rs.getString("examState")); 
				 

				exam_db_list.add(exam_db);
			}

			return exam_db_list;

		} catch (ClassNotFoundException | IOException | SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	@Override
	public int addExamByExam(Exam exam) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		try {
			conn = JdbcUtils.getConnection();
			String sql = "INSERT INTO  exam (examId, examName,examType,examState) VALUES(?,?,?,?)";
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, exam.getExamId());
			pstmt.setString(2, exam.getExamName());
			pstmt.setString(3, exam.getExamType());
			pstmt.setString(4, exam.getExamState());
			
			 

			int m = pstmt.executeUpdate();
			return m;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				pstmt.close();
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return 0;
	}

	@Override
	public int deleteExamByExamId(int examId) {
		Connection conn = null;
		PreparedStatement pstmt = null;

		try {
			conn = JdbcUtils.getConnection();

			String sql = "DELETE FROM exam WHERE examId=?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, examId);

			int m = pstmt.executeUpdate();
			return m;
		} catch (Exception e) {
			e.printStackTrace();
		}
		return 0;
	}

	@Override
	public Exam queryExamByExamId(int examId) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		// 查询数据库
		try {
			conn = JdbcUtils.getConnection();
			String sql = "SELECT * FROM exam WHERE examId=?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, examId);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				Exam exam_db = new Exam();
				exam_db.setExamId(rs.getInt("examId"));
				exam_db.setExamName(rs.getString("examName"));
				exam_db.setExamType(rs.getString("examType"));
				exam_db.setExamState(rs.getString("examState"));
				 
				return exam_db;
			}
		} catch (ClassNotFoundException | IOException | SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	@Override
	public int updateExamByExam(Exam exam) {
		Connection conn = null;
		PreparedStatement pstmt = null;

		try {
			conn = JdbcUtils.getConnection();
			String sql = "UPDATE exam SET examName=?,examType=?,examState=? WHERE examId=?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, exam.getExamName());
			pstmt.setString(2, exam.getExamType());
			pstmt.setString(3, exam.getExamState());
			pstmt.setInt(4, exam.getExamId());

			int m = pstmt.executeUpdate();
			return m;

		} catch (Exception e) {
			e.printStackTrace();
		}
		return 0;
	}

	@Override
	public int queryUser2TotalRecord() {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;

		try {
			conn = JdbcUtils.getConnection();
			String sql = "SELECT count(*) FROM exam";
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			if (rs.next()) {
				int count = rs.getInt(1);
				return count;
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return 0;
	}

	@Override
	public List<Exam> queryCurrentPageDataList(int i, int ps) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			conn = JdbcUtils.getConnection();
			String sql = "SELECT * FROM  exam LIMIT ?,?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, i);
			pstmt.setInt(2, ps);
			rs = pstmt.executeQuery();
			List<Exam> examList = new ArrayList<>();
			while (rs.next()) {
				Exam exam_db = new Exam();
				exam_db.setExamId(rs.getInt("examId"));
				exam_db.setExamName(rs.getString("examName"));
				exam_db.setExamType(rs.getString("examType"));
				exam_db.setExamState(rs.getString("examState"));
				examList.add(exam_db);
			}
			return examList;
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}

	
	
	
	////////-------------list------------------//////////////////////////
	@Override
	public List<PaiRoom> queryPrByExamId() {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		// 查询数据库
		try {
			conn = JdbcUtils.getConnection();
			String sql = "SELECT exam.examId, exam.examName, exam.examType, room_exam.date, room_exam.time, classroom.roomName FROM room_exam INNER JOIN exam ON exam.examId = room_exam.examId INNER JOIN classroom ON room_exam.roomld = classroom.roomId";
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			List<PaiRoom> pairoom_db_list = new ArrayList<PaiRoom>();

			while (rs.next()) {
				PaiRoom pairoom_db = new PaiRoom();
				pairoom_db.setExamId(rs.getInt("exam.examId"));
				pairoom_db.setExamName(rs.getString("exam.examName"));
				pairoom_db.setExamType(rs.getString("exam.examType"));
				pairoom_db.setDate(rs.getString("room_exam.date"));
				pairoom_db.setTime(rs.getString("room_exam.time"));
				pairoom_db.setRoomName(rs.getString("classroom.roomName"));
				
				

				pairoom_db_list.add(pairoom_db);
			}

			return pairoom_db_list;

		} catch (ClassNotFoundException | IOException | SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

    ////////-------------addUI2------------------//////////////////////////
	public List<Exam> queryAllExamByState(String examState) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		// 查询数据库
		try {
			conn = JdbcUtils.getConnection();
			String sql = "SELECT * FROM exam where examState=?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1,examState);
			rs = pstmt.executeQuery();

			List<Exam> exam_db_list = new ArrayList<Exam>();

			while (rs.next()) {
				Exam exam_db = new Exam();
				exam_db.setExamId(rs.getInt("examId"));
				exam_db.setExamName(rs.getString("examName"));
				exam_db.setExamType(rs.getString("examType"));
				exam_db.setExamState(rs.getString("examState"));
				exam_db_list.add(exam_db);
			}

			return exam_db_list;

		} catch (ClassNotFoundException | IOException | SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

}
